--CREATE DATABASE Smart_Blast;

USE Smart_Blast

CREATE TABLE Components(
Serial_Number INT primary key,
Name Varchar(100),
Description Text,
Quality INT,
Quantity INT,
Min_Quantity INT);

INSERT INTO Components
VALUES (11111, 'Amplifier', 'S-580B Amplifier', 7, 50, 10),
	   (22222, 'Cabel', 'Black in cable', 5, 300, 50),
	   (33333, 'Screw', 'A screw', 3, 1000, 200),
	   (44444, 'Sub', 'B-850S Subwoofer', 8, 35, 5),
	   (55555, 'Button', 'Volume button', 5, 100, 30)

CREATE TABLE Products(
Model Varchar(10) primary key,
Price INT,
Quantity INT);

INSERT INTO Products
VALUES ('SBS-A200', 250, 15),
	   ('SBS-A500', 550, 8)


CREATE TABLE Components_In_Products(
Product_Model Varchar(10),
Component_Serial_Number INT,
Quantity INT,
CONSTRAINT FK_Components_In_Products_Product foreign key (Product_Model) references Products(Model),
CONSTRAINT FK_Components_In_Products_Component foreign key (Component_serial_Number) references Components(Serial_Number),
CONSTRAINT PK_Components_In_Products primary key (Product_Model, Component_Serial_Number));

INSERT INTO Components_In_Products
VALUES ('SBS-A200', 11111, 1),
	   ('SBS-A200', 22222, 8),
	   ('SBS-A200', 33333, 20),
	   ('SBS-A200', 55555, 1),
	   ('SBS-A500', 11111, 2),
	   ('SBS-A500', 22222, 16),
	   ('SBS-A500', 33333, 50),
	   ('SBS-A500', 44444, 1),
	   ('SBS-A500', 55555, 2)

CREATE TABLE Employees(
Id INT primary key,
Name Varchar(100),
Username Varchar(32) unique,
Password Varchar(32),
Job Varchar(50),
Address Varchar(100),
Email Varchar (100),
Birth_Date Date,
Permission INT);

INSERT INTO Employees
VALUES (1, 'Sersi', 'Sersi', 'Queen01', 'Manager', 'Kings Landing 11', 'SersiQueen@Kingdom.com', '1.1.85', 2),
	   (2, 'Jon', 'Jon', 'KnowNothing5', 'QA', 'The Wall 82/12', 'Jon@Snow.com', '1.2.90', 5),
	   (3, 'Tirion', 'Tirion', 'SmathTheBeetle12', 'Administrator', 'UnKnown', 'Midget@gmail.com', '1.3.88', 1),
	   (4, 'Ned', 'Ned', 'WinterIsComming', 'Manufecturing', 'Tomb 15', 'Ned_NoHead@Honor.com', '1.4.67', 3)

CREATE TABLE Expenditures(
Name Varchar(20),
Date Date,
Description Text,
Amount INT,
CONSTRAINT PK_Expenditures primary key (Name, Date));

INSERT INTO Expenditures
VALUES ('Electricity', GETDATE(), 'Expensive electricity', 1000),
	   ('Arnona', GETDATE(), 'We have to be somewhere', 700),
	   ('Taxes', GETDATE(), 'By the law', 5500)

CREATE TABLE Customers(
Id INT primary key,
Name Varchar(100),
Address Varchar(30),
Email Varchar(30),
Phone_Number Varchar(10));

INSERT INTO Customers
VALUES (9, 'Aria', 'No Where 52', 'ValarMorgolis@Doharis.com', '0123456789'),
	   (8, 'Jimmy', 'Kings Landing 11', 'GoldHand@BangSis.com', '9876543210'),
	   (7, 'Peter', 'Eyrie', 'LittleFinger@LiarCo.com', '0000000000')


CREATE TABLE Orders(
Customer_Id INT,
Product_Model Varchar(10),
Date Datetime,
Quantity INT,
CONSTRAINT FK_Orders_Customer foreign key (Customer_Id) references Customers(Id),
CONSTRAINT FK_Orders_Component foreign key (Product_Model) references Products(Model),
CONSTRAINT PK_Orders primary key (Customer_Id, Product_Model, Date));

INSERT INTO Orders
VALUES (7, 'SBS-A200', GETDATE() - 1, 1),
	   (8, 'SBS-A200', GETDATE() - 3, 3),
	   (8, 'SBS-A500', GETDATE() - 3, 1),
	   (9, 'SBS-A500', GETDATE() - 4, 2),
	   (9, 'SBS-A500', GETDATE() - 6, 1)

SELECT * FROM [dbo].[Components]
SELECT * FROM [dbo].[Products]
SELECT * FROM [dbo].[Components_In_Products]
SELECT * FROM [dbo].[Employees]
SELECT * FROM [dbo].[Expenditures]
SELECT * FROM [dbo].[Customers]
SELECT * FROM [dbo].[Orders]